package com.mapproject.industrymap.service.impl;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.mapproject.industrymap.service.JDBCOperationService;
import com.mapproject.industrymap.utils.JDBCOperation;
import com.mapproject.industrymap.utils.ReturnValue;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
@Service
public class JDBCOperationServiceImpl implements JDBCOperationService {
    @Autowired
    JDBCOperation jdbcOperation;
    /**
     * @method Integer getAll() 查询并打印表中数据
     * @return Integer 查询并打印表中数据
     */
    public ReturnValue getColumnname(String tablename) {
        Connection conn = jdbcOperation.getConn();
        String sql = "select array_to_json(array_agg(column_name)) from information_schema.columns where table_name =" +"\'"+ tablename+"\'";
        PreparedStatement pstmt;
        try {
            pstmt = (PreparedStatement)conn.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();
            int col = rs.getMetaData().getColumnCount();
            while (rs.next()) {
                return new ReturnValue(200,"查询成功", JSON.parse(rs.getString(1)));
            }

        } catch (SQLException e) {
            e.printStackTrace();

        }
        return new ReturnValue(500,"查询失败");
    }
    public ReturnValue getColumnValue(String tablename, String columname){
        Connection conn = jdbcOperation.getConn();
        //postGIS中字符串大写需要加双引号，否则无法识别
        String sql ="select row_to_json(fd)from (select count(distinct \""+columname+"\") as count, array_to_json(array_agg( distinct \""+ columname+"\" ))as columnvalue  from "+tablename+" ) as fd" ;
        PreparedStatement pstmt;
        try {
            pstmt = (PreparedStatement)conn.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();
            int col = rs.getMetaData().getColumnCount();
            while (rs.next()) {
                JSONObject jsonObject=JSON.parseObject(rs.getString(1));
                int count= Integer.parseInt(jsonObject.get("count").toString()) ;
                if(count>30){
                    return new ReturnValue(500,"查询值超过30");
                }
                return new ReturnValue(200,"查询成功",jsonObject.get("columnvalue"));
            }
        } catch (SQLException e) {
            e.printStackTrace();

        }
        return new ReturnValue(500,"查询失败");
    }
}
